package com.jcl.recruit.searchresume.mapper;

import com.jcl.recruit.searchresume.model.SearchResume;
import com.jcl.recruit.searchresume.model.SearchResumeRecord;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author 刘明新
 * @date 2018/8/3 上午9:56
 */
public interface SearchResumeMapper {


    @Select({
            "<script>",
            "select * from search_resume where id not in ",
            "<foreach collection=\"ids\" item=\"id\" open=\"(\" close=\")\" separator=\",\"> " ,
            "  #{id} ",
            "</foreach> ",
            "</script>"
    })
    List<SearchResume> findListByNotInId(@Param("ids") List<Integer> ids);

    @Select({
            "<script>",
            "select distinct * from search_resume where",
            "<if test=\"keyword != null\">(realname LIKE '%${keyword}%' or work_last_company LIKE '%${keyword}%' or work_last_position LIKE '%${keyword}%'",
            "or edu_last_school LIKE '%${keyword}%') and</if>",
            "<if test=\"eduLastEducation != null and eduLastEducation != ''\">edu_last_education = #{eduLastEducation} and</if>",
            "<if test=\"city != null\">current_city LIKE '%${city}%' and</if>",
            "<if test=\"workyear == 1\">workyear = 0 and</if>",
            "<if test=\"workyear == 2\">workyear &lt;= 3 and workyear &gt;= 1 and</if>",
            "<if test=\"workyear == 3\">workyear &lt;= 5 and workyear &gt;= 3 and</if>",
            "<if test=\"workyear == 4\">workyear &gt;= 5 and</if>",
            "<if test=\"status != null\">status = #{status} and</if>",
            "1=1",
            "order by update_date desc,createtime desc",
            "</script>"
    })
    List<Map<String,Object>> selectSearchResumesByCondition(@Param("keyword") String keyword, @Param("eduLastEducation") String eduLastEducation, @Param("city") String city,
                                                            @Param("workyear") Integer workyear, @Param("status") Integer status);


    @Select({
            "<script>",
            "select distinct * from search_resume ",
            "<where>",
            "<if test=\"channel != null\"> and source_sub=#{channel}</if>",
            "<if test=\"records != null\"> and ",
            "<foreach collection=\"records\" item=\"record\" open=\"(\" close=\")\" separator=\"or\"> " ,
            "  (<if test=\"record.keywords != null \"> ",
            "    <foreach collection=\"record.keywords\" item=\"rkey\"> " ,
            "     CONCAT( IFNULL(keyword,''), IFNULL(realname,''), IFNULL(work_last_company,''), IFNULL(work_last_position,''), IFNULL(edu_last_school,''), IFNULL(evaluate,'')) LIKE '%${rkey}%' and ",
            "    </foreach> ",
            "  </if>",
            "  <if test=\"record.education != null and record.education != ''\"> edu_last_education = #{record.education} and </if>",
            "  <if test=\"record.city != null and record.city != '' and record.city != '全国'\"> current_city LIKE '%${record.city}%' and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 1\">  workyear = 0 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 2\"> workyear &lt;= 3 and workyear &gt;= 1 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 3\"> workyear &lt;= 5 and workyear &gt;= 3 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 4\"> workyear &gt;= 5 and </if>",
            "  1=1)",
            "</foreach> ",
            "</if>",
            "</where>",
            "order by update_date desc ,createtime desc",
            "</script>"
    })
    List<Map<String,Object>> selectRecommendSearchResumesByCondition(@Param("records") List<SearchResumeRecord> records,
                                                                     @Param("channel") String channel);

    @Select({
            "<script>",
            "select distinct A.* from search_resume A",
            "<where>",
            "<if test=\"channel != null\"> and A.source_sub=#{channel}</if>",
            "<if test=\"records != null\"> and ",
            "  (<if test=\"record.keywords != null \"> ",
            "    <foreach collection=\"record.keywords\" item=\"rkey\"> " ,
            "     CONCAT( IFNULL(keyword,''), IFNULL(realname,''), IFNULL(work_last_company,''), IFNULL(work_last_position,''), IFNULL(edu_last_school,''), IFNULL(evaluate,'')) LIKE '%${rkey}%' and ",
            "    </foreach> ",
            "  </if>",
            "  <if test=\"record.education != null and record.education != ''\"> edu_last_education = #{record.education} and </if>",
            "  <if test=\"record.city != null and record.city != '' and record.city != '全国'\"> current_city LIKE '%${record.city}%' and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 1\">  workyear = 0 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 2\"> workyear &lt;= 3 and workyear &gt;= 1 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 3\"> workyear &lt;= 5 and workyear &gt;= 3 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 4\"> workyear &gt;= 5 and </if>",
            "  1=1)",
            "</if>",
            "</where>",
            "order by update_date desc ,createtime desc",
            "</script>"
    })
    List<Map<String,Object>> selectRecommendSearchResumesByRecord(@Param("record") SearchResumeRecord record,
                                                                  @Param("channel") String channel);

    @Select({
            "<script>",
            "select count(*) as total from search_resume ",
            "WHERE createtime BETWEEN CONCAT(DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL -1 DAY), '%Y-%m-%d'), ' 08:00:00') AND CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d'), ' 08:00:00')",
            "<if test=\"records != null\"> and ",
            "<foreach collection=\"records\" item=\"record\" open=\"(\" close=\")\" separator=\"or\"> " ,
            "  (<if test=\"record.keywords != null \"> ",
            "    <foreach collection=\"record.keywords\" item=\"rkey\"> " ,
            "     CONCAT( IFNULL(keyword,''), IFNULL(realname,''), IFNULL(work_last_company,''), IFNULL(work_last_position,''), IFNULL(edu_last_school,''), IFNULL(evaluate,'')) LIKE '%${rkey}%' and ",
            "    </foreach> ",
            "  </if>",
            "  <if test=\"record.education != null and record.education != ''\"> edu_last_education = #{record.education} and </if>",
            "  <if test=\"record.city != null and record.city != '' and record.city != '全国'\"> current_city LIKE '%${record.city}%' and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 1\">  workyear = 0 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 2\"> workyear &lt;= 3 and workyear &gt;= 1 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 3\"> workyear &lt;= 5 and workyear &gt;= 3 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 4\"> workyear &gt;= 5 and </if>",
            "  1=1) ",
            "</foreach> ",
            "</if>",
            "</script>"
    })
    Map<String,Object> selectRecommendSearchResumesCountByCondition(@Param("records") List<SearchResumeRecord> records);

    @Select({
            "<script>",
            "select count(*) as total from search_resume ",
            "WHERE createtime BETWEEN #{startTime} AND #{endTime}",
            "<if test=\"records != null\"> and ",
            "<foreach collection=\"records\" item=\"record\" open=\"(\" close=\")\" separator=\"or\"> " ,
            "  (<if test=\"record.keywords != null \"> ",
            "    <foreach collection=\"record.keywords\" item=\"rkey\"> " ,
            "     CONCAT( IFNULL(keyword,''), IFNULL(realname,''), IFNULL(work_last_company,''), IFNULL(work_last_position,''), IFNULL(edu_last_school,''), IFNULL(evaluate,'')) LIKE '%${rkey}%' and ",
            "    </foreach> ",
            "  </if>",
            "  <if test=\"record.education != null and record.education != ''\"> edu_last_education = #{record.education} and </if>",
            "  <if test=\"record.city != null and record.city != '' and record.city != '全国'\"> current_city LIKE '%${record.city}%' and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 1\">  workyear = 0 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 2\"> workyear &lt;= 3 and workyear &gt;= 1 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 3\"> workyear &lt;= 5 and workyear &gt;= 3 and </if>",
            "  <if test=\"record.workYear != null and record.workYear == 4\"> workyear &gt;= 5 and </if>",
            "  1=1) ",
            "</foreach> ",
            "</if>",
            "</script>"
    })
    Map<String,Object> testRecommendSearchResumesCountByCondition(@Param("records") List<SearchResumeRecord> records,
                                                                  @Param("startTime") Date startTime,
                                                                  @Param("endTime") Date endTime);


    @Select({
            "select * from search_resume where id = #{id}"
    })
    Map<String,Object> selectPrimaryKeyById(@Param("id") Integer id);

    @Select({
            "select * from search_resume where id = #{id}"
    })
    SearchResume selectSearchResumeById(@Param("id") Integer id);

    @InsertProvider(type=SearchResumeSqlProvider.class, method="insertSelective")
    @Options(useGeneratedKeys=true,keyProperty="id")
    int insertSelective(SearchResume record);

    @UpdateProvider(type=SearchResumeSqlProvider.class, method="updateByPrimaryKeySelective")
    int updateByPrimaryKeySelective(SearchResume record);

    @Select({
            "select count(*) from search_resume where realname = #{entity.realname} and work_last_position = #{entity.workLastPosition} and work_last_company = #{entity.workLastCompany}",
            "and  edu_last_school = #{entity.eduLastSchool} and edu_last_education = #{entity.eduLastEducation}"
    })
    int selectCountBySearchResume(@Param("entity") SearchResume entity);

    @Select({
            "select count(*) from search_resume where birthday = #{entity.birthday} and desire_salary_start = #{entity.desireSalaryStart}",
            "and  desire_salary_end = #{entity.desireSalaryEnd} and workyear = #{entity.workyear} and work_last_position = #{entity.workLastPosition} and work_last_company = #{entity.workLastCompany}",
            "and edu_last_education = #{entity.eduLastEducation} and source_sub = 'zhipin'"
    })
    int selectCountByZhiPinSearchResume(@Param("entity") SearchResume entity);

    @Select({
            "<script>",
            "select DATE_FORMAT(createtime,'%Y-%m-%d') as '日期',source_sub as '来源',count(*) as '数量' from search_resume",
            "<if test=\"today\">where createtime > CURDATE()</if>",
            "group by",
            "source_sub,DATE_FORMAT(createtime,'%Y-%m-%d') order by DATE_FORMAT(createtime,'%Y-%m-%d'),source_sub",
            "</script>"
    })
    List<Map<String,String>> selectPrimarykeyByDatesAndSources(@Param("today") Boolean today);

    @Select({
            "select id,evaluate from search_resume where evaluate is not null and evaluate != '' group by evaluate HAVING count(*) > 1 order by id"
    })
    List<SearchResume> selectChongfu();

    @Delete({
            "<script>",
            "<foreach collection='list' item='entity' index='index' separator=';'>",
            "delete from search_resume where evaluate = #{entity.evaluate} and id != #{entity.id}",
            "</foreach>",
            "</script>"
    })
    int batchDelete(@Param("list") List<SearchResume> list);

    @Delete({
            "<script>",
            "delete from search_resume where id in ",
            "<foreach collection='ids' item='id' separator=',' open='(' close=')' >",
            " #{id} ",
            "</foreach>",
            "</script>"
    })
    int batchDeleteByIds(@Param("ids") List<Integer> ids);
}
